﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaProject;

namespace CinemaDbLib
{
    public class UserDB
    {
        private Connection connection;

        public UserDB()
        {
            connection = new Connection();
        }

        public List<User> getUsers()
        {
            return where("");
        }

        public List<User> getUsersSearch(string name)
        {
            return where("name LIKE " + name);
        }

        public User getUserById(int id)
        {
            return singleWhere("id = " + id);
        }

        public User getUserByUsername(string username)
        {
            return singleWhere("email='" + username + "'");
        }

        private User singleWhere(String wClause)
        {
            User user = null;

            string query = "SELECT * FROM XpUsers WHERE " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);
            if (reader.Read())
            {
                user = new User();

                user._id = reader.GetInt32(0);
                user._email = reader.GetString(1);
                user._name = reader.GetString(2);
                user._studieRabat = reader.GetBoolean(3);
                user._filmRabat = reader.GetBoolean(4);
                user._seniorRabat = reader.GetBoolean(5);
            }

            reader.Close();
            connection.Close();

            return user;
        }

        private List<User> where(String wClause)
        {
            List<User> users = new List<User>();

            string query = "SELECT * FROM XpUsers " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            while (reader.Read())
            {
                User user = new User();

                user._id = reader.GetInt32(0);
                user._email = reader.GetString(1);
                user._name = reader.GetString(3);
                user._studieRabat = reader.GetBoolean(4);
                user._filmRabat = reader.GetBoolean(5);
                user._seniorRabat = reader.GetBoolean(6);

                users.Add(user);
            }

            reader.Close();
            connection.Close();

            return users;
        }

        public void removeUser(int id)
        {
            string query = "DELETE FROM XpUsers WHERE id=" + id;
            
            connection.Open();
            connection.Execute(query);

            connection.Close();
        }

        public void insertUser(User user)
        {
            string query = @"INSERT INTO XpUsers (id, email, name, studieRabat, filmRabat, seniorRabat) " +
                "VALUES ( " + user._id + ", '" + user._email
                + "', '" + user._name + "', '" + user._studieRabat + "', '" + user._filmRabat + "', '" + user._seniorRabat + "')";
            
            connection.Open();
            connection.Execute(query);
            
            connection.Close();
        }

        public void updateUser(User user)
        {
            string query = @"UPDATE XpUsers SET name='" + user._name +
                "', email='" + user._email +
                "', studieRabat='" + user._studieRabat +
                "', filmRabat='" + user._filmRabat +
                "', seniorRabat='" + user._seniorRabat +
                "' WHERE id='" + user._id;
            
            connection.Open();
            connection.Execute(query);

            connection.Close();
        }
    }
}
